<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE topic PUBLIC "-//OASIS//DTD DITA Topic//EN" "topic.dtd">
<topic id="topic_lv4_czp_fz">
  <title>dblink</title>
  <body>
    <p>The <codeph>dblink</codeph> module supports connections to other Greenplum Database
      databases from within a database session. These databases can reside in the same Greenplum
      Database system, or in a remote system.</p>
      <p>Greenplum Database supports <codeph>dblink</codeph> connections between databases
        in Greenplum Database installations with the same major version number. You can also
        use <codeph>dblink</codeph> to connect to other Greenplum Database installations that
        use compatible <codeph>libpq</codeph> libraries.</p>
      <note><codeph>dblink</codeph> is intended for database users to perform short ad hoc queries in
        other databases. <codeph>dblink</codeph> is not intended for use as a replacement for external
        tables or for administrative tools such as <codeph>gpcopy</codeph>.</note>
    <p>The Greenplum Database <codeph>dblink</codeph> module is a modified version of the
       PostgreSQL <codeph>dblink</codeph> module. There are some restrictions and
       limitations when you use the module in Greenplum Database.</p>
  </body>
  <topic id="topic_reg">
    <title>Installing and Registering the Module</title>
    <body>
      <p>The <codeph>dblink</codeph> module is installed when you install
        Greenplum Database. Before you can use any of the functions defined in the
        module, you must register the <codeph>dblink</codeph> extension in
        each database in which you want to use the functions.
        <ph otherprops="pivotal">Refer to <xref href="../../install_guide/install_modules.html"
          format="html" scope="external">Installing Additional Supplied Modules</xref>
        for more information.</ph></p>
    </body>
  </topic>
  <topic id="topic_mpp">
    <title>Greenplum Database Considerations</title>
    <body>
      <p>In this release of Greenplum Database, statements that modify table data cannot use named
        or implicit <codeph>dblink</codeph> connections. Instead, you must provide the connection
        string directly in the <codeph>dblink()</codeph> function. For
        example:<codeblock>gpadmin=# CREATE TABLE testdbllocal (a int, b text) DISTRIBUTED BY (a);
CREATE TABLE
gpadmin=# INSERT INTO testdbllocal select * FROM dblink('dbname=postgres', 'SELECT * FROM testdblink') AS dbltab(id int, product text);
INSERT 0 2</codeblock></p>
      <p>The Greenplum Database version of <codeph>dblink</codeph> disables the following
        asynchronous functions:<ul id="ul_ajr_lsm_bdb">
          <li><codeph>dblink_send_query()</codeph></li>
          <li><codeph>dblink_is_busy()</codeph></li>
          <li><codeph>dblink_get_result()</codeph></li>
        </ul></p>
    </body>
  </topic>
  <topic id="topic_using">
    <title>Using dblink</title>
    <body>
      <p>The following procedure identifies the basic steps for configuring and using
          <codeph>dblink</codeph> in Greenplum Database. The examples use
          <codeph>dblink_connect()</codeph> to create a connection to a database and
          <codeph>dblink()</codeph> to run an SQL query. </p>
      <ol id="ol_axw_csm_bdb">
        <li>Begin by creating a sample table to query using the <codeph>dblink</codeph> functions.
          These commands create a small table in the <codeph>postgres</codeph> database, which you
          will later query from the <codeph>testdb</codeph> database using
          <codeph>dblink</codeph>:<codeblock>$ psql -d postgres
psql (9.4.20)
Type "help" for help.

postgres=# CREATE TABLE testdblink (a int, b text) DISTRIBUTED BY (a);
CREATE TABLE
postgres=# INSERT INTO testdblink VALUES (1, 'Cheese'), (2, 'Fish');
INSERT 0 2
postgres=# \q
$</codeblock></li>
        <li>Log into a different database as a superuser. In this example, the superuser
            <codeph>gpadmin</codeph> logs into the database <codeph>testdb</codeph>. If the
            <codeph>dblink</codeph> functions are not already available, register the
            <codeph>dblink</codeph> extension in the
          database:<codeblock>$ psql -d testdb
psql (9.4beta1)
Type "help" for help.

testdb=# CREATE EXTENSION dblink;
CREATE EXTENSION</codeblock></li>
        <li>Use the <codeph>dblink_connect()</codeph> function to create either an implicit or a
          named connection to another database. The connection string that you provide should be a
            <codeph>libpq</codeph>-style keyword/value string. This example creates a connection
          named <codeph>mylocalconn</codeph> to the <codeph>postgres</codeph> database on the local
          Greenplum Database system:<codeblock>testdb=# SELECT dblink_connect('mylocalconn', 'dbname=postgres user=gpadmin');
 dblink_connect
----------------
 OK
(1 row)</codeblock>
          <note>If a <codeph>user</codeph> is not specified, <codeph>dblink_connect()</codeph> uses
            the value of the <codeph>PGUSER</codeph> environment variable when Greenplum Database
            was started. If <codeph>PGUSER</codeph> is not set, the default is the system user that
            started Greenplum Database.</note></li>
        <li>Use the <codeph>dblink()</codeph> function to query a database using a configured
          connection. Keep in mind that this function returns a record type, so you must assign the
          columns returned in the <codeph>dblink()</codeph> query. For example, the following
          command uses the named connection to query the table you created
          earlier:<codeblock>testdb=# SELECT * FROM dblink('mylocalconn', 'SELECT * FROM testdblink') AS dbltab(id int, product text);
 id | product
----+---------
  1 | Cheese
  2 | Fish
(2 rows)</codeblock></li>
      </ol>
      <p>To connect to the local database as another user, specify the <codeph>user</codeph> in the
        connection string. This example connects to the database as the user
          <codeph>test_user</codeph>. Using <codeph>dblink_connect()</codeph>, a superuser can
        create a connection to another local database without specifying a password.</p>
      <codeblock>testdb=# SELECT dblink_connect('localconn2', 'dbname=postgres user=test_user');</codeblock>
      <p>To make a connection to a remote database system, include host and password information in
        the connection string. For example, to create an implicit <codeph>dblink</codeph> connection
        to a remote
        system:<codeblock>testdb=# SELECT dblink_connect('host=remotehost port=5432 dbname=postgres user=gpadmin password=secret');</codeblock></p>
      <section id="dblink_u">
        <title>Using dblink as a Non-Superuser</title>
        <p>To make a connection to a database with <codeph>dblink_connect()</codeph>, non-superusers
          must include host, user, and password information in the connection string. The host,
          user, and password information must be included even when connecting to a local database.
          You must also include an entry in <codeph>pg_hba.conf</codeph> for this non-superuser and
          the target database. For example, the user <codeph>test_user</codeph> can create a
            <codeph>dblink</codeph> connection to the local system <codeph>mdw</codeph> with this
          command:<codeblock>testdb=> SELECT dblink_connect('host=mdw port=5432 dbname=postgres user=test_user password=secret');</codeblock></p>
        <p>If non-superusers need to create <codeph>dblink</codeph> connections that do not require
          a password, they can use the <codeph>dblink_connect_u()</codeph> function. The
            <codeph>dblink_connect_u()</codeph> function is identical to
            <codeph>dblink_connect()</codeph>, except that it allows non-superusers to create
          connections that do not require a password. </p>
        <p><codeph>dblink_connect_u()</codeph> is initially installed with all privileges
          revoked from <codeph>PUBLIC</codeph>, making it un-callable except by superusers.
          In some situations, it may be appropriate to grant <codeph>EXECUTE</codeph> permission on
          <codeph>dblink_connect_u()</codeph> to specific users who are considered trustworthy,
          but this should be done with care.</p>
        <note type="warning">If a Greenplum Database system has configured users with an
          authentication method that does not involve a password, then impersonation and subsequent
          escalation of privileges can occur when a non-superuser runs
            <codeph>dblink_connect_u()</codeph>. The <codeph>dblink</codeph> connection will appear
          to have originated from the user specified by the function. For example, a non-superuser
          can run <codeph>dblink_connect_u()</codeph> and specify a user that is configured with
            <codeph>trust</codeph> authentication. <p>Also, even if the <codeph>dblink</codeph>
            connection requires a password, it is possible for the password to be supplied from the
            server environment, such as a <codeph>~/.pgpass</codeph> file belonging to the server's
            user. It is recommended that any <codeph>~/.pgpass</codeph> file belonging to the
            server's user not contain any records specifying a wildcard host name.</p></note>
        <ol id="ol_dpt_ll3_5fb">
          <li>As a superuser, grant the <codeph>EXECUTE</codeph> privilege on the
              <codeph>dblink_connect_u()</codeph> functions in the user database. This example
            grants the privilege to the non-superuser <codeph>test_user</codeph> on the functions
            with the signatures for creating an implicit or a named <codeph>dblink</codeph>
            connection. The server and database will be identified through a standard
              <codeph>libpq</codeph> connection string and optionally, a name can be assigned to the
            connection.<codeblock>testdb=# GRANT EXECUTE ON FUNCTION dblink_connect_u(text) TO test_user;
testdb=# GRANT EXECUTE ON FUNCTION dblink_connect_u(text, text) TO test_user;</codeblock></li>
          <li>Now <codeph>test_user</codeph> can create a connection to another local database
            without a password. For example, <codeph>test_user</codeph> can log into the
              <codeph>testdb</codeph> database and run this command to create a connection named
              <codeph>testconn</codeph> to the local <codeph>postgres</codeph> database.<codeblock>testdb=> SELECT dblink_connect_u('testconn', 'dbname=postgres user=test_user');</codeblock>
            <note>If a <codeph>user</codeph> is not specified, <codeph>dblink_connect_u()</codeph>
              uses the value of the <codeph>PGUSER</codeph> environment variable when Greenplum
              Database was started. If <codeph>PGUSER</codeph> is not set, the default is the system
              user that started Greenplum Database.</note></li>
          <li><codeph>test_user</codeph> can use the <codeph>dblink()</codeph> function to run a
            query using a <codeph>dblink</codeph> connection. For example, this command uses the
              <codeph>dblink</codeph> connection named <codeph>testconn</codeph> created in the
            previous step. <codeph>test_user</codeph> must have appropriate access to the
            table.<codeblock>testdb=> SELECT * FROM dblink('testconn', 'SELECT * FROM testdblink') AS dbltab(id int, product text);</codeblock></li>
        </ol>
      </section>
      <section>
        <title>Using dblink as a Non-Superuser without Authentication Checks</title>
        <p>In rare cases you may need to allow non-superusers to acccess to <codeph>dblink</codeph>
          without making any authentication checks. The function
            <codeph>dblink_connect_no_auth()</codeph> provides this functionality as it bypasses the
            <codeph>pg_hba.conf</codeph> file. </p>
        <note type="warning">Using this function introduces a security risk; ensure that you grant
          unauthorized access only to trusted user accounts. Also note that
            <codeph>dblink_connect_no_auth()</codeph> functions limit connections to the local
          cluster, and do not permit connections to a remote database.</note>
        <p>These functions are not available by default; the <codeph>gpadmin</codeph> superuser must
          grant permission to the non-superuser beforehand:</p>
        <ol id="ol_agf_qwj_x4b">
          <li>As a superuser, grant the <codeph>EXECUTE</codeph> privilege on the
              <codeph>dblink_connect_no_auth()</codeph> functions in the user database. This example
            grants the privilege to the non-superuser <codeph>test_user</codeph> on the functions
            with the signatures for creating an implicit or a named <codeph>dblink</codeph>
            connection.<codeblock>testdb=# GRANT EXECUTE ON FUNCTION dblink_connect_no_auth(text) TO test_user;
testdb=# GRANT EXECUTE ON FUNCTION dblink_connect_no_auth(text, text) TO test_user;</codeblock></li>
          <li>Now <codeph>test_user</codeph> can create a connection to another local database
            without providing a password, regardless of what is specified in
              <codeph>pg_hba.conf</codeph>. For example, <codeph>test_user</codeph> can log into the
              <codeph>testdb</codeph> database and execute this command to create a connection named
              <codeph>testconn</codeph> to the local <codeph>postgres</codeph>
            database.<codeblock>testdb=> SELECT dblink_connect_no_auth('testconn', 'dbname=postgres user=test_user');</codeblock>
          </li>
          <li><codeph>test_user</codeph> can use the <codeph>dblink()</codeph> function to execute a
            query using a <codeph>dblink</codeph> connection. For example, this command uses the
              <codeph>dblink</codeph> connection named <codeph>testconn</codeph> created in the
            previous step. <codeph>test_user</codeph> must have appropriate access to the
            table.<codeblock>testdb=> SELECT * FROM dblink('testconn', 'SELECT * FROM testdblink') AS dbltab(id int, product text);</codeblock></li>
        </ol>
      </section>
      <section id="dblink_ssl">
        <title>Using dblink with SSL-Encrypted Connections to Greenplum</title>
        <p>When you use <codeph>dblink</codeph> to connect to Greenplum Database
          over an encrypted connection, you must specify the <codeph>sslmode</codeph>
          property in the connection string. Set <codeph>sslmode</codeph> to at
          least <codeph>require</codeph> to disallow unencrypted transfers. For example:</p>
        <codeblock>testdb=# SELECT dblink_connect('greenplum_con_sales', 'dbname=sales host=gpmaster user=gpadmin sslmode=require');</codeblock>
        <p>Refer to <xref href="../../security-guide/topics/Authenticate.xml#topic_fzv_wb2_jr/ssl_postgresql"
          format="dita" scope="peer">SSL Client Authentication</xref> for
          information about configuring Greenplum Database to use SSL.</p>
      </section>
    </body>
  </topic>
  <topic id="topic_info">
    <title>Additional Module Documentation</title>
    <body>
      <p>Refer to the <xref href="https://www.postgresql.org/docs/9.4/dblink.html" format="html"
        scope="external">dblink</xref> PostgreSQL documentation for detailed
        information about the individual functions in this module.</p>
    </body>
  </topic>
</topic>
